set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;
INSERT OVERWRITE TABLE jms_dm.dm_agent_operate_bi_month_dt PARTITION (dt)
SELECT
      manage_code,     --管理大区code
      max(manage_name) as manage_name,     --管理大区名字
      agent_code,      --代理区code
      max(agent_name) as agent_name,      --代理区名字
      sum(qxt_bill_count) as qxt_bill_count,  --七星潭平台业务量
      sum(thd_bill_count) as thd_bill_count,  --桃花岛平台业务量
      sum(zjs_bill_count) as zjs_bill_count,  --紫金山平台业务量
      sum(xyf_bill_count) as xyf_bill_count,  --逍遥峰平台业务量
      sum(qt_bill_count) as qt_bill_count,   --其它平台业务量
      sum(pick_waybill_cnt) as pick_waybill_cnt, --收件量
      sum(departure_total) as departure_total,    --发车总个数
      sum(punctuality_sum) as punctuality_sum,  --准时发车个数
      sum(not_punctuality_sum) as not_punctuality_sum,  --晚点发车个数
      sum(arrival_total) as arrival_total,        -- 干线到车数
      sum(arrival_late_sum) as arrival_late_sum,     ---干线到车晚点数
      sum(arrival_not_late_sum) as arrival_not_late_sum,  --干线准时到车个数
      sum(deliver_waybill_sum) as deliver_waybill_sum,    --派件总量
      sum(sign_cnt) as sign_cnt,               --签收总量
      sum(new_network_cnt) as new_network_cnt,  --新增网点个数
      sum(total_network_cnt) as total_network_cnt, --网点总数
      sum(not_operate_network_cnt) as not_operate_network_cnt,   --未运营网点数
      trunc('{{ execution_date | cst_ds }}','MM') as business_date,                --日期
      sum(jdw_bill_count) as jdw_bill_count,    -- 极地湾平台业务量
      '{{ execution_date | cst_ds }}' as dt                                                      --日期
from jms_dm.dm_agent_operate_bi_dt                      --平台业务量数据
where dt>=trunc('{{ execution_date | cst_ds }}','MM')
and dt<='{{ execution_date | cst_ds }}'
group by manage_code,agent_code
distribute by 1;